#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
Created on Sat Nov 13 20:02:12 2021

@author: Aiping
"""

##### create table ##########################









import sqlite3

conn = sqlite3.connect('test.db')
print ("Opened database successfully")
c = conn.cursor()
c.execute('''CREATE TABLE Company
        (ID INT PRIMARY KEY     NOT NULL,
        NAME           TEXT    NOT NULL,
        AGE            INT     NOT NULL,
        ADDRESS        CHAR(50),
        SALARY         REAL);''')
print ("Table created successfully")
conn.commit()
conn.close()





##### Insert to table ##########################

import sqlite3

conn = sqlite3.connect('test_1.db')
c = conn.cursor()
print('connected to the database successfully')

c.execute("INSERT INTO company VALUES (1, 'Paul', 32, 'California', 20000) ")

c.execute("INSERT INTO company VALUES (2, 'Allen', 25, 'Texas', 15000)")


conn.commit()
print('Records created successfully')
conn.close()








##### ManyInsert to table ##########################




import sqlite3

conn = sqlite3.connect('test.db',timeout = 10)
c = conn.cursor()
print('connected to database successfully')

many_records = [
                (3, 'Teddy', 23, 'Norway', 20000),
                (4, 'Mark', 25, 'Rich-Mond', 65000),]

c.executemany("INSERT INTO Company_2 VALUES (?,?,?,?,?)", many_records)


conn.commit()
print('Records created successfully')
conn.close()







##### Query and Fetch   #################################

import sqlite3

conn = sqlite3.connect('test_1.db')
c = conn.cursor()
print('connected successfully')

c_data = c.execute("SELECT id, name, address, salary from company")

for row in c_data:
    print("ID = ", row[0])
    print("NAME = ", row[1])
    print("ADDRESS = ", row[2])
    print("SALARY = ", row[3], '\n')
    
print("operation done successfully") 
conn.close()   



